// HSS.Logging.Management.SqlServices.cs
// ----------------------------------------------------------------------------
// Licensed under the MIT license
// http://www.opensource.org/licenses/mit-license.html
// ----------------------------------------------------------------------------
// HighSpeed-Solutions, LLC
// Copyright (c) 2001-2010
// ----------------------------------------------------------------------------
// File:       SqlServices.cs
// Author:     HSS\gbanta
// Created:    08/12/2010
// Modified:   12/04/2010
// ----------------------------------------------------------------------------
namespace HSS.Logging.Management
{
	#region Using Directives
	using System;
	using System.Collections.Generic;
	using System.Collections;
	using System.Globalization;
	using System.Text;
	using System.IO;
	using System.Data;
	using System.Data.SqlClient;
	using System.ComponentModel;
	using HSS.Data;
	#endregion

	#region SqlServices
	/// <summary>
	/// Supports installing and removing the SQL Server database elements for the HSS EventLog feature.
	/// </summary>
	internal static class SqlServices
	{
		#region Fields
		private static string DEFAULT_DB = "hsslog";
		#endregion

		#region Methods

		/// <summary>
		/// Get an SQL Services Script
		/// </summary>
		/// <param name="install">Install or Uninstall</param>
		/// <param name="database">The name of the Database</param>
		/// <returns>SQL Script Contents</returns>
		public static string GetSqlServicesScript(bool install, string database)
		{
			CheckDatabaseName(ref database);
			string content = string.Empty;
			if (install)
				content = HSS.Logging.Management.Resources.InstallHSSEventLog;
			else
				content = HSS.Logging.Management.Resources.UninstallHSSEventLog;
			content = FixContent(content, database);
			return content;
		}

		/// <summary>
		/// Install the SqlServices for the EventLog feature
		/// </summary>
		/// <param name="worker">The BackgroundWorker that this install is currently running under.</param>
		public static bool Install(BackgroundWorker worker)
		{
			return SetupSqlServices(true, worker);
		}
		/// <summary>
		/// Uninstall the SqlServices for the EventLog feature
		/// </summary>
		/// <param name="worker">The BackgroundWorker that this uninstall is currently running under.</param>
		public static bool Uninstall(BackgroundWorker worker)
		{
			return SetupSqlServices(false, worker);
		}

		#region Helpers

		private static bool SetupSqlServices(bool install, BackgroundWorker worker)
		{
			if (worker.CancellationPending)
				return false;

			#region If we're uninstalling, ensure the database does not contain any data
			if (!install)
			{
				using (DataAccess da = new DataAccess(InstallWizard.ConnectionString))
				{
					string tableWithData = null;
					try
					{
						tableWithData = da.ExecScalar("hss_EventLog_AnyDataInTables") as string;
					}
					catch (SqlException exception)
					{
						if (exception.Number != 0xafc)
							throw;
					}
					if (!string.IsNullOrEmpty(tableWithData))
						throw new NotSupportedException(string.Format(HSS.Logging.Management.Resources.SQL_Services_Error_Cant_Uninstall_Nonempty_Table, tableWithData, InstallWizard.Database));
				}
			}
			#endregion

			if (worker.CancellationPending)
				return false;

			bool databaseCreated = false;
			bool DatabaseConfigured = false;
			
			#region If we're installing, ensure database exist
			if (install)
			{
				try
				{
					if (!InstallWizard.DatabaseExists)
					{
						using (DataAccess da = new DataAccess(InstallWizard.ConnectionString))
						{
							#region Create Database
							da.Connection.InitialCatalog = string.Empty;
							worker.ReportProgress(5, "Creating database...");
							string databaseCreateSQL = string.Format(HSS.Logging.Management.Resources.DatabaseCreateSQL, InstallWizard.Database);
							da.ExecNonQuerySQL(databaseCreateSQL);
							databaseCreated = true;
							worker.ReportProgress(10, "Creating database objects");
							#endregion
						}
					}
					else
					{
						worker.ReportProgress(10, "Creating database objects");
						InstallWizard.DatabaseExists = true;
					}
				}
				catch (Exception ex)
				{
					InstallWizard.DatabaseExists = false;
					InstallWizard.Error = ex;
					return false;
				}
			}
			#endregion

			using (SqlConnection sqlConnection = new SqlConnection(InstallWizard.ConnectionString))
			{
				#region Begin Transaction
				sqlConnection.Open();
				SqlTransaction transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted); // Start a local transaction.
				SqlCommand command = sqlConnection.CreateCommand();
				command.Transaction = transaction;
				#endregion

				try
				{
					string database = InstallWizard.Database;
					CheckDatabaseName(ref database);
					ExecuteScript(database, command, install, worker);
					worker.ReportProgress(80, "Installed committed...");
					transaction.Commit();
					DatabaseConfigured = true;
				}
				catch
				{
					#region Exception & Rollback

					DatabaseConfigured = false;

					// Attempt to roll back the transaction.
					try { transaction.Rollback(); }
					catch { }
					#endregion

					return false;

				}
				finally
				{
					if (!DatabaseConfigured && databaseCreated)
					{
						#region Drop Database
						try
						{
							using (DataAccess da = new DataAccess(InstallWizard.ConnectionString))
							{
								string singleUser = "ALTER DATABASE [" + InstallWizard.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
								da.ExecNonQuerySQL(singleUser);
								string databaseDropSQL = string.Format("drop database [{0}]", InstallWizard.Database);
								da.ExecNonQuerySQL(databaseDropSQL);
							}
						}
						catch { }
						#endregion
					}
				}
			}

			return true;
		}

		private static void ExecuteScript(string database, SqlCommand command, bool isInstall, BackgroundWorker worker)
		{
			int progress = 0;
			int cmdCount = 0;
			worker.ReportProgress(0, "Executing commands...");

			#region Prep
			string content = string.Empty;
			if (isInstall)
				content = HSS.Logging.Management.Resources.InstallHSSEventLog;
			else
				content = HSS.Logging.Management.Resources.UninstallHSSEventLog;
			content = FixContent(content, database);

			StringReader reader = new StringReader(content);
			command.Parameters.Clear();

			string commands = null;
			string scriptLine;

			#endregion

			Queue<string> cmds = new Queue<string>();

			#region Parse the Script File
			do
			{
				if (worker.CancellationPending)
					return;
				bool flag = false;
				scriptLine = reader.ReadLine();
				if (scriptLine == null)
					flag = true;
				else if (String.Equals(scriptLine.Trim().ToUpper(), "GO"))
					flag = true;
				else
				{
					if (commands != null)
						commands = commands + "\n";
					commands = commands + scriptLine;
				}
				if (flag & (commands != null))
				{
					cmds.Enqueue(commands);
					commands = null;
				}
			}
			while (scriptLine != null);
			#endregion

			int totalCmds = cmds.Count;
			string totalCmdStr = cmds.Count.ToString();
			double step = 100f / totalCmds;

			#region Execute the commands
			while (cmds.Count > 0)
			{
				if (worker.CancellationPending)
					return;

				string cmdText = cmds.Dequeue();
				command.CommandText = cmdText;

				try
				{
					cmdCount++;
					progress = (int)Convert.ChangeType(Math.Ceiling(cmdCount * step), typeof(int));
					worker.ReportProgress(progress, "Executing Script [ Statement " + cmdCount.ToString() + " of " + totalCmdStr + " ]");
					command.ExecuteNonQuery();
				}
				catch (Exception exception)
				{
					SqlException sqlException = exception as SqlException;
					if (sqlException != null)
						throw new SqlExecutionException(string.Format(System.Globalization.CultureInfo.CurrentUICulture, HSS.Logging.Management.Resources.SQL_Services_Error_Executing_Command, "", sqlException.Number, sqlException));
					else
						throw;
				}
			}
			#endregion

			#region OLD
			//do
			//{
			//    bool flag = false;
			//    scriptLine = reader.ReadLine();
			//    if (scriptLine == null)
			//        flag = true;
			//    else if (String.Equals(scriptLine.Trim().ToUpper(System.Globalization.CultureInfo.InvariantCulture), "GO"))
			//        flag = true;
			//    else
			//    {
			//        if (commands != null)
			//            commands = commands + "\n";
			//        commands = commands + scriptLine;
			//    }
			//    if (flag & (commands != null))
			//    {
			//        command.CommandText = commands;
			//        try
			//        {
			//            command.ExecuteNonQuery();
			//        }
			//        catch (Exception exception)
			//        {
			//            if (null != command)
			//                command.Dispose();

			//            if (null != reader)
			//                reader.Dispose();

			//            SqlException sqlException = exception as SqlException;
			//            if (sqlException != null)
			//                throw new SqlExecutionException(string.Format(System.Globalization.CultureInfo.CurrentUICulture, HSS.Logging.Management.Resources.SQL_Services_Error_Executing_Command, "", sqlException.Number, sqlException));
			//            else
			//                throw;
			//        }

			//        commands = null;
			//    }
			//}
			//while (scriptLine != null);
			#endregion

		}

		internal static bool DatabaseExists(DataAccess da, string databaseName)
		{
			string databasExistsSQL = string.Format("select db_id('{0}')", databaseName);
			object existsNameObj = da.ExecScalarSQL(databasExistsSQL);
			if ((existsNameObj == null) || (existsNameObj == DBNull.Value))
				return false;
			else
				return true;
		}
		internal static bool IsInstalled(DataAccess da)
		{
			object isInstalledBit = da.ExecScalarSQL(HSS.Logging.Management.Resources.IsHSSCoreInstalledSQL);
			if (DBNull.Value == isInstalledBit || null == isInstalledBit)
				return false;
			if ((string)isInstalledBit == "installed")
				return true;
			return false;
		}

		private static void CheckDatabaseName(ref string database)
		{
			if (database != null)
			{
				database = database.TrimEnd(new char[0]);
				if (database.Length == 0)
					throw new ArgumentException( HSS.Logging.Management.Resources.SQL_Services_Database_Empty_Or_Space_Only_Arg);
				database = RemoveSquareBrackets(database);
				if ((database.Contains("'") || database.Contains("[")) || database.Contains("]"))
					throw new ArgumentException( HSS.Logging.Management.Resources.SQL_Services_Database_contains_invalid_chars);
			}
			if (database == null)
				database = DEFAULT_DB;
			else
				database = "[" + database + "]";
		}
		private static string RemoveSquareBrackets(string database)
		{
			if (database != null && database.StartsWith("[") && database.EndsWith("]"))
				return database.Substring(1, database.Length - 2);
			return database;
		}
		private static void EnsureDatabaseExists(DataAccess da, string database)
		{
			if (DatabaseExists(da, database))
				throw new Exception(string.Format(System.Globalization.CultureInfo.CurrentUICulture, HSS.Logging.Management.Resources.SQL_Services_Error_Cant_Uninstall_Nonexisting_Database, database));
		}
		private static string FixContent(string content, string database)
		{
			if (database != null)
			{
				database = RemoveSquareBrackets(database);
				content = content.Replace("'hsslog'", "'" + database.Replace("'", "''") + "'");
				content = content.Replace("[hsslog]", "[" + database + "]");
			}

			return content;
		}

		#endregion

		#endregion
	}
	#endregion
}